{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "animated-passport",
   "metadata": {},
   "source": [
    "# 通用模板自动填充库应用演示\n",
    "\n",
    "> * 准备工作\n",
    ">   - 导入包\n",
    ">   - 模板文件\n",
    ">   - 填充定位\n",
    ">   - 填充内容文件\n",
    "> * 应用操作\n",
    ">   - 填充内容分割\n",
    ">     - 提取一对一映射表\n",
    ">     - 提取一对多映射表\n",
    ">     - 打包\n",
    ">   - 创建 Exsheet 对象\n",
    ">   - 读入模板\n",
    ">   - 准备空白 Excel 工作表\n",
    ">   - 在工作表中逐一放入模板块\n",
    ">     - 拆包填充内容\n",
    ">     - 新块\n",
    ">     - 填充内容\n",
    ">     - 放入 Excel 的 Sheet 表\n",
    ">   - 对 Excel 的 Sheet 进行布局设置\n",
    ">   - 保存为 Excel 文件\n",
    "___"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "suspended-glasgow",
   "metadata": {},
   "source": [
    "1. 准备工作\n",
    "<br>1.1 导入包"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "animated-interference",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from openpyxl import Workbook\n",
    "import fillpat as fp\n",
    "import fin"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "constant-residence",
   "metadata": {},
   "source": [
    "    1.2 模板文件"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "finished-archive",
   "metadata": {},
   "outputs": [],
   "source": [
    "pat = r'.\\datas\\费用报销单.xlsx'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "detected-weekly",
   "metadata": {},
   "source": [
    "    1.3 填充定位"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "opponent-crack",
   "metadata": {},
   "outputs": [],
   "source": [
    "oto_loc = {\n",
    "    'C3' :'公司名称',\n",
    "    'M3' :'请款部门',\n",
    "    'W3' :'填单日期',\n",
    "    'F11':'小写合计',\n",
    "    'M11':'大写合计',\n",
    "    'W11':'附件数量',\n",
    "    'E12':'是转工资',\n",
    "    'G12':'否转工资',\n",
    "    'K12':'原借支',\n",
    "    'O12':'现报销',\n",
    "    'W12':'剩余交回',\n",
    "    'K13':'收款账号',\n",
    "    'U13':'开户银行'\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "legal-drama",
   "metadata": {},
   "outputs": [],
   "source": [
    "mtm_loc = {\n",
    "    '6:10':'行范围',\n",
    "    'B':'事由或品名',\n",
    "    'I':'单位',\n",
    "    'J':'数量',\n",
    "    'L':'单价',\n",
    "    'M':'币种',\n",
    "    'N':'金额',\n",
    "    'W':'备注'\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "solar-density",
   "metadata": {},
   "source": [
    "    1.4 填充内容文件"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "macro-fleece",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>公司名称</th>\n",
       "      <th>请款部门</th>\n",
       "      <th>填单日期</th>\n",
       "      <th>事由或品名</th>\n",
       "      <th>单位</th>\n",
       "      <th>数量</th>\n",
       "      <th>单价</th>\n",
       "      <th>币种</th>\n",
       "      <th>金额</th>\n",
       "      <th>...</th>\n",
       "      <th>小写合计</th>\n",
       "      <th>附件数量</th>\n",
       "      <th>是转工资</th>\n",
       "      <th>否转工资</th>\n",
       "      <th>原借支</th>\n",
       "      <th>现报销</th>\n",
       "      <th>剩余交回</th>\n",
       "      <th>受款人</th>\n",
       "      <th>收款账号</th>\n",
       "      <th>开户银行</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>某公司</td>\n",
       "      <td>财务部</td>\n",
       "      <td>2022-03-29</td>\n",
       "      <td>订书机</td>\n",
       "      <td>个</td>\n",
       "      <td>5</td>\n",
       "      <td>10</td>\n",
       "      <td>人民币</td>\n",
       "      <td>50</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7</td>\n",
       "      <td>√</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>张三</td>\n",
       "      <td>2220 1298 2983 2981 337</td>\n",
       "      <td>建设银行</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>某公司</td>\n",
       "      <td>财务部</td>\n",
       "      <td>2022-03-29</td>\n",
       "      <td>打印机</td>\n",
       "      <td>台</td>\n",
       "      <td>2</td>\n",
       "      <td>2000</td>\n",
       "      <td>人民币</td>\n",
       "      <td>4000</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7</td>\n",
       "      <td>√</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>张三</td>\n",
       "      <td>2220 1298 2983 2981 337</td>\n",
       "      <td>建设银行</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>某公司</td>\n",
       "      <td>财务部</td>\n",
       "      <td>2022-03-29</td>\n",
       "      <td>鼠标</td>\n",
       "      <td>个</td>\n",
       "      <td>10</td>\n",
       "      <td>75</td>\n",
       "      <td>人民币</td>\n",
       "      <td>750</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7</td>\n",
       "      <td>√</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>张三</td>\n",
       "      <td>2220 1298 2983 2981 337</td>\n",
       "      <td>建设银行</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>某公司</td>\n",
       "      <td>财务部</td>\n",
       "      <td>2022-03-29</td>\n",
       "      <td>笔记本</td>\n",
       "      <td>本</td>\n",
       "      <td>3</td>\n",
       "      <td>20</td>\n",
       "      <td>人民币</td>\n",
       "      <td>60</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7</td>\n",
       "      <td>√</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>张三</td>\n",
       "      <td>2220 1298 2983 2981 337</td>\n",
       "      <td>建设银行</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>某公司</td>\n",
       "      <td>财务部</td>\n",
       "      <td>2022-03-29</td>\n",
       "      <td>回形针</td>\n",
       "      <td>盒</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>人民币</td>\n",
       "      <td>6</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7</td>\n",
       "      <td>√</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>张三</td>\n",
       "      <td>2220 1298 2983 2981 337</td>\n",
       "      <td>建设银行</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2</td>\n",
       "      <td>某公司</td>\n",
       "      <td>财务部</td>\n",
       "      <td>2022-03-29</td>\n",
       "      <td>试验</td>\n",
       "      <td>盒</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>人民币</td>\n",
       "      <td>6</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>√</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>李四</td>\n",
       "      <td>2220 1298 2983 2981 445</td>\n",
       "      <td>建设银行</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2</td>\n",
       "      <td>某公司</td>\n",
       "      <td>财务部</td>\n",
       "      <td>2022-03-29</td>\n",
       "      <td>A</td>\n",
       "      <td>a</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>人民币</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>√</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>李四</td>\n",
       "      <td>2220 1298 2983 2981 445</td>\n",
       "      <td>建设银行</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2</td>\n",
       "      <td>某公司</td>\n",
       "      <td>财务部</td>\n",
       "      <td>2022-03-29</td>\n",
       "      <td>B</td>\n",
       "      <td>b</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>人民币</td>\n",
       "      <td>4</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>√</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>李四</td>\n",
       "      <td>2220 1298 2983 2981 445</td>\n",
       "      <td>建设银行</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2</td>\n",
       "      <td>某公司</td>\n",
       "      <td>财务部</td>\n",
       "      <td>2022-03-29</td>\n",
       "      <td>C</td>\n",
       "      <td>c</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>人民币</td>\n",
       "      <td>9</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>√</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>李四</td>\n",
       "      <td>2220 1298 2983 2981 445</td>\n",
       "      <td>建设银行</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>3</td>\n",
       "      <td>某公司</td>\n",
       "      <td>财务部</td>\n",
       "      <td>2022-03-29</td>\n",
       "      <td>订书机</td>\n",
       "      <td>个</td>\n",
       "      <td>5</td>\n",
       "      <td>10</td>\n",
       "      <td>人民币</td>\n",
       "      <td>50</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7</td>\n",
       "      <td>√</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>张三</td>\n",
       "      <td>2220 1298 2983 2981 337</td>\n",
       "      <td>建设银行</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>3</td>\n",
       "      <td>某公司</td>\n",
       "      <td>财务部</td>\n",
       "      <td>2022-03-29</td>\n",
       "      <td>打印机</td>\n",
       "      <td>台</td>\n",
       "      <td>2</td>\n",
       "      <td>2000</td>\n",
       "      <td>人民币</td>\n",
       "      <td>4000</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7</td>\n",
       "      <td>√</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>张三</td>\n",
       "      <td>2220 1298 2983 2981 337</td>\n",
       "      <td>建设银行</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>3</td>\n",
       "      <td>某公司</td>\n",
       "      <td>财务部</td>\n",
       "      <td>2022-03-29</td>\n",
       "      <td>鼠标</td>\n",
       "      <td>个</td>\n",
       "      <td>10</td>\n",
       "      <td>75</td>\n",
       "      <td>人民币</td>\n",
       "      <td>750</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7</td>\n",
       "      <td>√</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>张三</td>\n",
       "      <td>2220 1298 2983 2981 337</td>\n",
       "      <td>建设银行</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>3</td>\n",
       "      <td>某公司</td>\n",
       "      <td>财务部</td>\n",
       "      <td>2022-03-29</td>\n",
       "      <td>笔记本</td>\n",
       "      <td>本</td>\n",
       "      <td>3</td>\n",
       "      <td>20</td>\n",
       "      <td>人民币</td>\n",
       "      <td>60</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7</td>\n",
       "      <td>√</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>张三</td>\n",
       "      <td>2220 1298 2983 2981 337</td>\n",
       "      <td>建设银行</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>3</td>\n",
       "      <td>某公司</td>\n",
       "      <td>财务部</td>\n",
       "      <td>2022-03-29</td>\n",
       "      <td>回形针</td>\n",
       "      <td>盒</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>人民币</td>\n",
       "      <td>6</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7</td>\n",
       "      <td>√</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>张三</td>\n",
       "      <td>2220 1298 2983 2981 337</td>\n",
       "      <td>建设银行</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>4</td>\n",
       "      <td>某公司</td>\n",
       "      <td>财务部</td>\n",
       "      <td>2022-03-29</td>\n",
       "      <td>试验</td>\n",
       "      <td>盒</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>人民币</td>\n",
       "      <td>6</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>√</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>李四</td>\n",
       "      <td>2220 1298 2983 2981 445</td>\n",
       "      <td>建设银行</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>4</td>\n",
       "      <td>某公司</td>\n",
       "      <td>财务部</td>\n",
       "      <td>2022-03-29</td>\n",
       "      <td>A</td>\n",
       "      <td>a</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>人民币</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>√</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>李四</td>\n",
       "      <td>2220 1298 2983 2981 445</td>\n",
       "      <td>建设银行</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>4</td>\n",
       "      <td>某公司</td>\n",
       "      <td>财务部</td>\n",
       "      <td>2022-03-29</td>\n",
       "      <td>B</td>\n",
       "      <td>b</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>人民币</td>\n",
       "      <td>4</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>√</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>李四</td>\n",
       "      <td>2220 1298 2983 2981 445</td>\n",
       "      <td>建设银行</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>4</td>\n",
       "      <td>某公司</td>\n",
       "      <td>财务部</td>\n",
       "      <td>2022-03-29</td>\n",
       "      <td>C</td>\n",
       "      <td>c</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>人民币</td>\n",
       "      <td>9</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>√</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>李四</td>\n",
       "      <td>2220 1298 2983 2981 445</td>\n",
       "      <td>建设银行</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>18 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    ID 公司名称 请款部门       填单日期 事由或品名 单位  数量    单价   币种    金额  ... 小写合计  附件数量  \\\n",
       "0    1  某公司  财务部 2022-03-29   订书机  个   5    10  人民币    50  ...  NaN     7   \n",
       "1    1  某公司  财务部 2022-03-29   打印机  台   2  2000  人民币  4000  ...  NaN     7   \n",
       "2    1  某公司  财务部 2022-03-29    鼠标  个  10    75  人民币   750  ...  NaN     7   \n",
       "3    1  某公司  财务部 2022-03-29   笔记本  本   3    20  人民币    60  ...  NaN     7   \n",
       "4    1  某公司  财务部 2022-03-29   回形针  盒   3     2  人民币     6  ...  NaN     7   \n",
       "5    2  某公司  财务部 2022-03-29    试验  盒   3     2  人民币     6  ...  NaN     1   \n",
       "6    2  某公司  财务部 2022-03-29     A  a   1     1  人民币     1  ...  NaN     1   \n",
       "7    2  某公司  财务部 2022-03-29     B  b   2     2  人民币     4  ...  NaN     1   \n",
       "8    2  某公司  财务部 2022-03-29     C  c   3     3  人民币     9  ...  NaN     1   \n",
       "9    3  某公司  财务部 2022-03-29   订书机  个   5    10  人民币    50  ...  NaN     7   \n",
       "10   3  某公司  财务部 2022-03-29   打印机  台   2  2000  人民币  4000  ...  NaN     7   \n",
       "11   3  某公司  财务部 2022-03-29    鼠标  个  10    75  人民币   750  ...  NaN     7   \n",
       "12   3  某公司  财务部 2022-03-29   笔记本  本   3    20  人民币    60  ...  NaN     7   \n",
       "13   3  某公司  财务部 2022-03-29   回形针  盒   3     2  人民币     6  ...  NaN     7   \n",
       "14   4  某公司  财务部 2022-03-29    试验  盒   3     2  人民币     6  ...  NaN     1   \n",
       "15   4  某公司  财务部 2022-03-29     A  a   1     1  人民币     1  ...  NaN     1   \n",
       "16   4  某公司  财务部 2022-03-29     B  b   2     2  人民币     4  ...  NaN     1   \n",
       "17   4  某公司  财务部 2022-03-29     C  c   3     3  人民币     9  ...  NaN     1   \n",
       "\n",
       "    是转工资  否转工资   原借支 现报销  剩余交回  受款人                     收款账号  开户银行  \n",
       "0      √   NaN  4000 NaN   NaN   张三  2220 1298 2983 2981 337  建设银行  \n",
       "1      √   NaN  4000 NaN   NaN   张三  2220 1298 2983 2981 337  建设银行  \n",
       "2      √   NaN  4000 NaN   NaN   张三  2220 1298 2983 2981 337  建设银行  \n",
       "3      √   NaN  4000 NaN   NaN   张三  2220 1298 2983 2981 337  建设银行  \n",
       "4      √   NaN  4000 NaN   NaN   张三  2220 1298 2983 2981 337  建设银行  \n",
       "5    NaN     √     0 NaN   NaN   李四  2220 1298 2983 2981 445  建设银行  \n",
       "6    NaN     √     0 NaN   NaN   李四  2220 1298 2983 2981 445  建设银行  \n",
       "7    NaN     √     0 NaN   NaN   李四  2220 1298 2983 2981 445  建设银行  \n",
       "8    NaN     √     0 NaN   NaN   李四  2220 1298 2983 2981 445  建设银行  \n",
       "9      √   NaN  4000 NaN   NaN   张三  2220 1298 2983 2981 337  建设银行  \n",
       "10     √   NaN  4000 NaN   NaN   张三  2220 1298 2983 2981 337  建设银行  \n",
       "11     √   NaN  4000 NaN   NaN   张三  2220 1298 2983 2981 337  建设银行  \n",
       "12     √   NaN  4000 NaN   NaN   张三  2220 1298 2983 2981 337  建设银行  \n",
       "13     √   NaN  4000 NaN   NaN   张三  2220 1298 2983 2981 337  建设银行  \n",
       "14   NaN     √     0 NaN   NaN   李四  2220 1298 2983 2981 445  建设银行  \n",
       "15   NaN     √     0 NaN   NaN   李四  2220 1298 2983 2981 445  建设银行  \n",
       "16   NaN     √     0 NaN   NaN   李四  2220 1298 2983 2981 445  建设银行  \n",
       "17   NaN     √     0 NaN   NaN   李四  2220 1298 2983 2981 445  建设银行  \n",
       "\n",
       "[18 rows x 22 columns]"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fil = pd.read_excel(r'.\\datas\\费用报销记录.xlsx')\n",
    "fil"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "widespread-announcement",
   "metadata": {},
   "source": [
    "2. 应用操作：\n",
    "<br>2.1 填充内容分割\n",
    "   <br>2.1.1 提取一对一映射表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "major-education",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['ID',\n",
       " '公司名称',\n",
       " '请款部门',\n",
       " '填单日期',\n",
       " '小写合计',\n",
       " '大写合计',\n",
       " '附件数量',\n",
       " '是转工资',\n",
       " '否转工资',\n",
       " '原借支',\n",
       " '现报销',\n",
       " '剩余交回',\n",
       " '收款账号',\n",
       " '开户银行']"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "oto_col = list(oto_loc.values())\n",
    "oto_col = ['ID'] + oto_col\n",
    "oto_col"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "consecutive-design",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>公司名称</th>\n",
       "      <th>请款部门</th>\n",
       "      <th>填单日期</th>\n",
       "      <th>小写合计</th>\n",
       "      <th>大写合计</th>\n",
       "      <th>附件数量</th>\n",
       "      <th>是转工资</th>\n",
       "      <th>否转工资</th>\n",
       "      <th>原借支</th>\n",
       "      <th>现报销</th>\n",
       "      <th>剩余交回</th>\n",
       "      <th>收款账号</th>\n",
       "      <th>开户银行</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>某公司</td>\n",
       "      <td>财务部</td>\n",
       "      <td>2022-03-29</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7</td>\n",
       "      <td>√</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2220 1298 2983 2981 337</td>\n",
       "      <td>建设银行</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>某公司</td>\n",
       "      <td>财务部</td>\n",
       "      <td>2022-03-29</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>√</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2220 1298 2983 2981 445</td>\n",
       "      <td>建设银行</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>某公司</td>\n",
       "      <td>财务部</td>\n",
       "      <td>2022-03-29</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7</td>\n",
       "      <td>√</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2220 1298 2983 2981 337</td>\n",
       "      <td>建设银行</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>某公司</td>\n",
       "      <td>财务部</td>\n",
       "      <td>2022-03-29</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>√</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2220 1298 2983 2981 445</td>\n",
       "      <td>建设银行</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   公司名称 请款部门       填单日期  小写合计  大写合计  附件数量 是转工资 否转工资   原借支  现报销  剩余交回  \\\n",
       "ID                                                                     \n",
       "1   某公司  财务部 2022-03-29   NaN   NaN     7    √  NaN  4000  NaN   NaN   \n",
       "2   某公司  财务部 2022-03-29   NaN   NaN     1  NaN    √     0  NaN   NaN   \n",
       "3   某公司  财务部 2022-03-29   NaN   NaN     7    √  NaN  4000  NaN   NaN   \n",
       "4   某公司  财务部 2022-03-29   NaN   NaN     1  NaN    √     0  NaN   NaN   \n",
       "\n",
       "                       收款账号  开户银行  \n",
       "ID                                 \n",
       "1   2220 1298 2983 2981 337  建设银行  \n",
       "2   2220 1298 2983 2981 445  建设银行  \n",
       "3   2220 1298 2983 2981 337  建设银行  \n",
       "4   2220 1298 2983 2981 445  建设银行  "
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "adatas = fil[oto_col].drop_duplicates().set_index('ID')\n",
    "adatas"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "undefined-contrast",
   "metadata": {},
   "source": [
    "**完善合计数**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "initial-validation",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>公司名称</th>\n",
       "      <th>请款部门</th>\n",
       "      <th>填单日期</th>\n",
       "      <th>小写合计</th>\n",
       "      <th>大写合计</th>\n",
       "      <th>附件数量</th>\n",
       "      <th>是转工资</th>\n",
       "      <th>否转工资</th>\n",
       "      <th>原借支</th>\n",
       "      <th>现报销</th>\n",
       "      <th>剩余交回</th>\n",
       "      <th>收款账号</th>\n",
       "      <th>开户银行</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>某公司</td>\n",
       "      <td>财务部</td>\n",
       "      <td>2022-03-29</td>\n",
       "      <td>4866</td>\n",
       "      <td>肆仟捌佰陆拾陆元整</td>\n",
       "      <td>7</td>\n",
       "      <td>√</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2220 1298 2983 2981 337</td>\n",
       "      <td>建设银行</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>某公司</td>\n",
       "      <td>财务部</td>\n",
       "      <td>2022-03-29</td>\n",
       "      <td>20</td>\n",
       "      <td>贰拾元整</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>√</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2220 1298 2983 2981 445</td>\n",
       "      <td>建设银行</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>某公司</td>\n",
       "      <td>财务部</td>\n",
       "      <td>2022-03-29</td>\n",
       "      <td>4866</td>\n",
       "      <td>肆仟捌佰陆拾陆元整</td>\n",
       "      <td>7</td>\n",
       "      <td>√</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2220 1298 2983 2981 337</td>\n",
       "      <td>建设银行</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>某公司</td>\n",
       "      <td>财务部</td>\n",
       "      <td>2022-03-29</td>\n",
       "      <td>20</td>\n",
       "      <td>贰拾元整</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>√</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2220 1298 2983 2981 445</td>\n",
       "      <td>建设银行</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   公司名称 请款部门       填单日期  小写合计       大写合计  附件数量 是转工资 否转工资   原借支  现报销  剩余交回  \\\n",
       "ID                                                                          \n",
       "1   某公司  财务部 2022-03-29  4866  肆仟捌佰陆拾陆元整     7    √  NaN  4000  NaN   NaN   \n",
       "2   某公司  财务部 2022-03-29    20       贰拾元整     1  NaN    √     0  NaN   NaN   \n",
       "3   某公司  财务部 2022-03-29  4866  肆仟捌佰陆拾陆元整     7    √  NaN  4000  NaN   NaN   \n",
       "4   某公司  财务部 2022-03-29    20       贰拾元整     1  NaN    √     0  NaN   NaN   \n",
       "\n",
       "                       收款账号  开户银行  \n",
       "ID                                 \n",
       "1   2220 1298 2983 2981 337  建设银行  \n",
       "2   2220 1298 2983 2981 445  建设银行  \n",
       "3   2220 1298 2983 2981 337  建设银行  \n",
       "4   2220 1298 2983 2981 445  建设银行  "
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "adatas['小写合计'] = fil.groupby('ID')['金额'].sum()\n",
    "adatas['大写合计'] = adatas.小写合计.apply(fin.amount_converter)\n",
    "adatas"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "pending-dining",
   "metadata": {},
   "source": [
    "    2.1.2 提取一对多映射表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "placed-recommendation",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['ID', '事由或品名', '单位', '数量', '单价', '币种', '金额', '备注']"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mtm_col = ['ID'] + list(mtm_loc.values())[1:]\n",
    "mtm_col"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "spare-teddy",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>事由或品名</th>\n",
       "      <th>单位</th>\n",
       "      <th>数量</th>\n",
       "      <th>单价</th>\n",
       "      <th>币种</th>\n",
       "      <th>金额</th>\n",
       "      <th>备注</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>订书机</td>\n",
       "      <td>个</td>\n",
       "      <td>5</td>\n",
       "      <td>10</td>\n",
       "      <td>人民币</td>\n",
       "      <td>50</td>\n",
       "      <td>老板用</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>打印机</td>\n",
       "      <td>台</td>\n",
       "      <td>2</td>\n",
       "      <td>2000</td>\n",
       "      <td>人民币</td>\n",
       "      <td>4000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>鼠标</td>\n",
       "      <td>个</td>\n",
       "      <td>10</td>\n",
       "      <td>75</td>\n",
       "      <td>人民币</td>\n",
       "      <td>750</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>笔记本</td>\n",
       "      <td>本</td>\n",
       "      <td>3</td>\n",
       "      <td>20</td>\n",
       "      <td>人民币</td>\n",
       "      <td>60</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>回形针</td>\n",
       "      <td>盒</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>人民币</td>\n",
       "      <td>6</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2</td>\n",
       "      <td>试验</td>\n",
       "      <td>盒</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>人民币</td>\n",
       "      <td>6</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2</td>\n",
       "      <td>A</td>\n",
       "      <td>a</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>人民币</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2</td>\n",
       "      <td>B</td>\n",
       "      <td>b</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>人民币</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2</td>\n",
       "      <td>C</td>\n",
       "      <td>c</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>人民币</td>\n",
       "      <td>9</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>3</td>\n",
       "      <td>订书机</td>\n",
       "      <td>个</td>\n",
       "      <td>5</td>\n",
       "      <td>10</td>\n",
       "      <td>人民币</td>\n",
       "      <td>50</td>\n",
       "      <td>老板用</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>3</td>\n",
       "      <td>打印机</td>\n",
       "      <td>台</td>\n",
       "      <td>2</td>\n",
       "      <td>2000</td>\n",
       "      <td>人民币</td>\n",
       "      <td>4000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>3</td>\n",
       "      <td>鼠标</td>\n",
       "      <td>个</td>\n",
       "      <td>10</td>\n",
       "      <td>75</td>\n",
       "      <td>人民币</td>\n",
       "      <td>750</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>3</td>\n",
       "      <td>笔记本</td>\n",
       "      <td>本</td>\n",
       "      <td>3</td>\n",
       "      <td>20</td>\n",
       "      <td>人民币</td>\n",
       "      <td>60</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>3</td>\n",
       "      <td>回形针</td>\n",
       "      <td>盒</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>人民币</td>\n",
       "      <td>6</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>4</td>\n",
       "      <td>试验</td>\n",
       "      <td>盒</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>人民币</td>\n",
       "      <td>6</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>4</td>\n",
       "      <td>A</td>\n",
       "      <td>a</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>人民币</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>4</td>\n",
       "      <td>B</td>\n",
       "      <td>b</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>人民币</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>4</td>\n",
       "      <td>C</td>\n",
       "      <td>c</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>人民币</td>\n",
       "      <td>9</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    ID 事由或品名 单位  数量    单价   币种    金额   备注\n",
       "0    1   订书机  个   5    10  人民币    50  老板用\n",
       "1    1   打印机  台   2  2000  人民币  4000  NaN\n",
       "2    1    鼠标  个  10    75  人民币   750  NaN\n",
       "3    1   笔记本  本   3    20  人民币    60  NaN\n",
       "4    1   回形针  盒   3     2  人民币     6  NaN\n",
       "5    2    试验  盒   3     2  人民币     6  NaN\n",
       "6    2     A  a   1     1  人民币     1  NaN\n",
       "7    2     B  b   2     2  人民币     4  NaN\n",
       "8    2     C  c   3     3  人民币     9  NaN\n",
       "9    3   订书机  个   5    10  人民币    50  老板用\n",
       "10   3   打印机  台   2  2000  人民币  4000  NaN\n",
       "11   3    鼠标  个  10    75  人民币   750  NaN\n",
       "12   3   笔记本  本   3    20  人民币    60  NaN\n",
       "13   3   回形针  盒   3     2  人民币     6  NaN\n",
       "14   4    试验  盒   3     2  人民币     6  NaN\n",
       "15   4     A  a   1     1  人民币     1  NaN\n",
       "16   4     B  b   2     2  人民币     4  NaN\n",
       "17   4     C  c   3     3  人民币     9  NaN"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "datass = fil[mtm_col]\n",
    "datass"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "hollow-utility",
   "metadata": {},
   "source": [
    "    2.1.3 打包"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "third-spell",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'oto': 公司名称                        某公司\n",
       "  请款部门                        财务部\n",
       "  填单日期        2022-03-29 00:00:00\n",
       "  小写合计                       4866\n",
       "  大写合计                  肆仟捌佰陆拾陆元整\n",
       "  附件数量                          7\n",
       "  是转工资                          √\n",
       "  否转工资                        NaN\n",
       "  原借支                        4000\n",
       "  现报销                         NaN\n",
       "  剩余交回                        NaN\n",
       "  收款账号    2220 1298 2983 2981 337\n",
       "  开户银行                       建设银行\n",
       "  Name: 1, dtype: object,\n",
       "  'mtm':    ID 事由或品名 单位  数量    单价   币种    金额   备注\n",
       "  0   1   订书机  个   5    10  人民币    50  老板用\n",
       "  1   1   打印机  台   2  2000  人民币  4000  NaN\n",
       "  2   1    鼠标  个  10    75  人民币   750  NaN\n",
       "  3   1   笔记本  本   3    20  人民币    60  NaN\n",
       "  4   1   回形针  盒   3     2  人民币     6  NaN},\n",
       " {'oto': 公司名称                        某公司\n",
       "  请款部门                        财务部\n",
       "  填单日期        2022-03-29 00:00:00\n",
       "  小写合计                         20\n",
       "  大写合计                       贰拾元整\n",
       "  附件数量                          1\n",
       "  是转工资                        NaN\n",
       "  否转工资                          √\n",
       "  原借支                           0\n",
       "  现报销                         NaN\n",
       "  剩余交回                        NaN\n",
       "  收款账号    2220 1298 2983 2981 445\n",
       "  开户银行                       建设银行\n",
       "  Name: 2, dtype: object,\n",
       "  'mtm':    ID 事由或品名 单位  数量  单价   币种  金额   备注\n",
       "  5   2    试验  盒   3   2  人民币   6  NaN\n",
       "  6   2     A  a   1   1  人民币   1  NaN\n",
       "  7   2     B  b   2   2  人民币   4  NaN\n",
       "  8   2     C  c   3   3  人民币   9  NaN},\n",
       " {'oto': 公司名称                        某公司\n",
       "  请款部门                        财务部\n",
       "  填单日期        2022-03-29 00:00:00\n",
       "  小写合计                       4866\n",
       "  大写合计                  肆仟捌佰陆拾陆元整\n",
       "  附件数量                          7\n",
       "  是转工资                          √\n",
       "  否转工资                        NaN\n",
       "  原借支                        4000\n",
       "  现报销                         NaN\n",
       "  剩余交回                        NaN\n",
       "  收款账号    2220 1298 2983 2981 337\n",
       "  开户银行                       建设银行\n",
       "  Name: 3, dtype: object,\n",
       "  'mtm':     ID 事由或品名 单位  数量    单价   币种    金额   备注\n",
       "  9    3   订书机  个   5    10  人民币    50  老板用\n",
       "  10   3   打印机  台   2  2000  人民币  4000  NaN\n",
       "  11   3    鼠标  个  10    75  人民币   750  NaN\n",
       "  12   3   笔记本  本   3    20  人民币    60  NaN\n",
       "  13   3   回形针  盒   3     2  人民币     6  NaN},\n",
       " {'oto': 公司名称                        某公司\n",
       "  请款部门                        财务部\n",
       "  填单日期        2022-03-29 00:00:00\n",
       "  小写合计                         20\n",
       "  大写合计                       贰拾元整\n",
       "  附件数量                          1\n",
       "  是转工资                        NaN\n",
       "  否转工资                          √\n",
       "  原借支                           0\n",
       "  现报销                         NaN\n",
       "  剩余交回                        NaN\n",
       "  收款账号    2220 1298 2983 2981 445\n",
       "  开户银行                       建设银行\n",
       "  Name: 4, dtype: object,\n",
       "  'mtm':     ID 事由或品名 单位  数量  单价   币种  金额   备注\n",
       "  14   4    试验  盒   3   2  人民币   6  NaN\n",
       "  15   4     A  a   1   1  人民币   1  NaN\n",
       "  16   4     B  b   2   2  人民币   4  NaN\n",
       "  17   4     C  c   3   3  人民币   9  NaN}]"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fil_com = []\n",
    "for idx,adata in adatas.iterrows():\n",
    "    datas = datass[datass['ID']==idx]\n",
    "    combine = {'oto': adata, 'mtm': datas}\n",
    "    fil_com.append(combine)\n",
    "fil_com"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "found-vitamin",
   "metadata": {},
   "source": [
    "    2.2 创建 Exsheet 对象"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "civilian-settle",
   "metadata": {},
   "outputs": [],
   "source": [
    "exs = fp.Exsheet(\n",
    "    gap_between_blocks_in_a_page=2,\n",
    "    number_of_blocks_per_page=2\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "expressed-federal",
   "metadata": {},
   "source": [
    "    2.3 读入模板"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "competitive-aerospace",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<fillpat.Exblock at 0x164535a36a0>"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "exs.read_pattern(pat)\n",
    "exs.pattern"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "painful-expert",
   "metadata": {},
   "source": [
    "    2.4 准备空白 Excel 工作表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "phantom-litigation",
   "metadata": {},
   "outputs": [],
   "source": [
    "wb = Workbook()\n",
    "ws = wb.active"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "hawaiian-vaccine",
   "metadata": {},
   "source": [
    "    2.5 在工作表中逐一放入模板块"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "announced-siemens",
   "metadata": {},
   "outputs": [],
   "source": [
    "len = adatas.shape[0]\n",
    "for i in range(0, len):\n",
    "    # 2.5.1 拆包\n",
    "    adata = fil_com[i]['oto']\n",
    "    datas = fil_com[i]['mtm']\n",
    "    # 2.5.2 新块\n",
    "    exs.Id = i + 1\n",
    "    # 2.5.3 填充\n",
    "    exs.fill_blank(oto_loc, adata, mtm_loc, datas)\n",
    "    # 2.5.4  放入 Sheet\n",
    "    exs.put_in_sheet(ws)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "distinct-herald",
   "metadata": {},
   "source": [
    "    2.6 对 Excel 的 Sheet 进行布局设置"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "steady-edgar",
   "metadata": {},
   "outputs": [],
   "source": [
    "exs.page_layout(ws, margins=exs.NARROWER,scale=85 ,verticalcentered=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "false-inside",
   "metadata": {},
   "source": [
    "    2.7 保存为 Excel 文件"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "arctic-pickup",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "done\n"
     ]
    }
   ],
   "source": [
    "wb.save('输出报销单.xlsx')\n",
    "print('done')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "broke-college",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
